# _*_ coding:utf-8 _*_
# @File  : exchange.py
# @Time  : 2021-12-02  13:04
# @Author: zizle
import datetime

import pandas as pd
from fastapi import APIRouter, Body, Query
from pydantic import BaseModel, constr, conint
from db import FAConnection

from status import r_status
from category import VARIETY_NAME
from tasks.holidays import holidays

# pd.set_option('display.max_rows', None)  # 行

investment_exchange_api = APIRouter()


def no_build_exchange():  # 不能建仓和平仓的时间，周末节假日和每日的11点-12点
    today = datetime.datetime.today()
    if today.strftime('%Y-%m-%d') in holidays or today.weekday() >= 5:  # 周末节假日不能建平仓
        return False
    else:
        return '22:58:00' <= datetime.datetime.now().strftime('%H:%M:%S') <= '23:59:59'


# 计算记录的价格差
def calculate_price_diff(direction, price, close_price):
    return close_price - price if (direction == '多' or direction == '平多') else price - close_price


# 处理数据小数问题
def format_number(x, p):
    return int(x) if int(x) == float(x) else round(x, p)


class BuildItem(BaseModel):
    build_date: constr(min_length=10, max_length=10)
    account: constr(min_length=15, max_length=15)
    variety_en: constr(min_length=1, max_length=2)
    contract: constr(min_length=5, max_length=6)
    bail_rate: float
    hands: conint(ge=1)
    hand_amount: float
    price_type: str
    price: float
    direction: str


# 建仓操作
@investment_exchange_api.post('/holding/', summary='投资号建仓操作')
async def build_position(build_item: BuildItem = Body(...)):
    # 每日22:58:00~23:59:59不支持建仓
    if no_build_exchange():
        return {'code': r_status.FORBIDDEN, 'message': '周末节假日及系统统计数据期间不支持建仓!'}
    try:
        build_ts = int(datetime.datetime.strptime(build_item.build_date, '%Y-%m-%d').timestamp())
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': '操作日期格式错误!'}
    # 验证数据
    if build_item.price_type not in ['open', 'close', 'zdy']:
        return {'code': r_status.VALIDATE_ERROR, 'message': '操作价格类型选择错误!'}
    if build_item.direction not in ['多', '空']:
        return {'code': r_status.VALIDATE_ERROR, 'message': '操作类型选择错误!'}
    if build_item.price <= 0 or build_item.hands <= 0:
        return {'code': r_status.VALIDATE_ERROR, 'message': '操作价格或手数错误!'}
    """
    建仓操作：1.影响账户表的保证金占用 2. 增加交易记录表的记录 3. 处理最新持仓表的记录
    """
    db_conn = FAConnection(conn_name='投资管理建仓')
    # 查询账户
    sql = 'SELECT id,account_name,account,r_fund,b_fund FROM user_invest_account WHERE account=%s LIMIT 1;'
    obj = db_conn.query(sql, param=[build_item.account], fetchone=True, keep_conn=True)[0]
    if not obj:
        db_conn.close()
        return {'code': r_status.VALIDATE_ERROR, 'message': '账户不存在，操作失败!'}

    available = round(obj['r_fund'] - obj['b_fund'], 2)  # 可用资金
    bail_needed = build_item.price * build_item.hands * build_item.bail_rate * build_item.hand_amount
    if bail_needed > available:
        db_conn.close()
        return {'code': r_status.VALIDATE_ERROR, 'message': '保证金不足，操作失败!'}

    # 查询最新行情数据
    latest_sql = 'SELECT a.quotes_ts,a.contract,a.open_price,a.close_price ' \
                 'FROM dat_futures_daily_quotes AS a ' \
                 'WHERE a.quotes_ts=(SELECT MAX(quotes_ts) FROM dat_futures_daily_quotes);'
    # db_conn = FAConnection(conn_name='查询持仓')
    latest_quotes = db_conn.query(latest_sql, keep_conn=True)
    if build_ts < latest_quotes[0]['quotes_ts']:
        db_conn.close()
        return {'code': r_status.VALIDATE_ERROR, 'message': '不能建以前日期的仓位，操作失败!'}
    # 行情数据处理成dict
    # quotes_date = datetime.datetime.fromtimestamp(latest_quotes[0]['quotes_ts']).strftime('%Y-%m-%d')
    quotes_data = {f'{item["contract"]}': item["close_price"] for item in latest_quotes}

    # 查询操作日权益表是否存在
    profit_exist_sql = 'SELECT id,create_ts FROM user_invest_profit WHERE account=%s AND create_ts=%s;'
    profit_exist_obj = db_conn.query(profit_exist_sql, param=[build_item.account, build_ts], fetchone=True, keep_conn=True)[0]
    # 四步事务型操作：
    # 1 增加明细表
    # 2 汇总表是否存在，不存在增加，存在更新
    # 3 更新账户表
    # 4 处理权益表

    # 查询汇总表(暨持仓表)
    position_query_sql = 'SELECT id,account,contract,direction,o_price,o_hands,hand_amount,bail_rate ' \
                         'FROM user_invest_position WHERE account=%s;'
    position_list = db_conn.query(position_query_sql, param=[build_item.account], keep_conn=True)
    # 得到该账户所有的持仓数据
    # 遍历
    exist_obj = None
    for p_item in position_list:
        if p_item['contract'] == build_item.contract and p_item['direction'] == build_item.direction:
            p_hands = build_item.hands + p_item['o_hands']
            build_price_avg = (p_item['o_hands'] * p_item['o_price'] + build_item.price * build_item.hands) / p_hands
            p_item['o_hands'] = p_hands
            p_item['o_price'] = build_price_avg
            exist_obj = p_item
            break
    # 判断是否存在，决定处理仓位表的sql
    if exist_obj:  # 则更新记录
        position_sql = 'UPDATE user_invest_position SET o_price=%s,o_hands=%s WHERE id=%s LIMIT 1;'
        position_param = [round(exist_obj['o_price'], 2), exist_obj['o_hands'], exist_obj['id']]

    else:  # 新的则新增
        # 向权益表对象增加一个item
        position_list.append({
            'account': build_item.account, 'contract': build_item.contract, 'direction': build_item.direction,
            'o_price': build_item.price, 'o_hands': build_item.hands, 'hand_amount': build_item.hand_amount,
            'bail_rate': build_item.bail_rate
        })
        position_sql = 'INSERT INTO user_invest_position (account,variety_en,contract,direction,o_price,o_hands,' \
                       'hand_amount,bail_rate) VALUES (%s,%s,%s,%s,%s,%s,%s,%s);'
        position_param = [build_item.account, build_item.variety_en, build_item.contract, build_item.direction,
                          build_item.price, build_item.hands, build_item.hand_amount, build_item.bail_rate]

    # # 计算当前状态下总的浮动收益和保证金占用  (2021-12-28权益计算需今日结束后再进行计算)
    # o_bail = 0
    # o_profit = 0
    # for p_item in position_list:
    #     quote_price = quotes_data.get(p_item['contract'], p_item['o_price'])
    #     diff_price = calculate_price_diff(p_item['direction'], p_item['o_price'], quote_price)
    #     o_profit += diff_price * p_item['hand_amount'] * p_item['o_hands']
    #     o_bail += p_item['o_price'] * p_item['o_hands'] * p_item['hand_amount'] * p_item['bail_rate']
    #
    # if profit_exist_obj:
    #     # print('权益存在')
    #     profit_sql = 'UPDATE user_invest_profit SET h_profit=%s WHERE id=%s LIMIT 1;'
    #     profit_param = [round(o_profit, 2), profit_exist_obj['id']]
    # else:
    #     profit_sql = 'INSERT INTO user_invest_profit (create_ts,account,h_profit,c_profit,total_profit,bail) ' \
    #                  'VALUES (%s,%s,%s,%s,%s,%s);'
    #     profit_param = [build_ts, build_item.account, round(o_profit, 2), 0, obj['r_fund'], round(o_bail, 2)]

    # 增加明细表  (2021-12-28明细表需增加明确到秒的时间)
    exchange_sql = 'INSERT INTO user_invest_exchange (account,variety_en,contract,direction,hands,' \
                   'hand_amount,price_type, o_price,c_price) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s);'
    exchange_param = [build_item.account, build_item.variety_en, build_item.contract, build_item.direction,
                      build_item.hands, build_item.hand_amount, build_item.price_type, build_item.price, 0]

    # 更新账户表
    account_bail = obj['b_fund'] + bail_needed  # 目前总所需保证金
    update_account_sql = 'UPDATE user_invest_account SET b_fund=%s WHERE account=%s;'
    update_account_param = [account_bail, build_item.account]

    # 执行事务
    success = db_conn.execute_tasks(sql_list=[exchange_sql, position_sql, update_account_sql],
                                    param_list=[exchange_param, position_param, update_account_param])
    if success:
        return {'code': r_status.SUCCESS, 'message': '操作成功!'}
    else:
        return {'code': r_status.SERVER_ERROR, 'message': '操作失败，服务器错误!'}


# 持仓的记录
@investment_exchange_api.get('/holding/', summary='投资号的持仓记录')
async def position_detail(account: str = Query(..., min_length=15, max_length=15)):
    # query_sql = 'SELECT t.id,t.create_ts,t.variety_en,t.contract,t.direction,t.o_price,t.o_hands,' \
    #             't.c_hands,t.bail_rate,t.hand_amount ' \
    #             'FROM user_invest_position AS t ' \
    #             'WHERE (t.create_ts,t.account,t.contract,t.direction) IN ' \
    #             '(SELECT MAX(create_ts),account,contract,direction ' \
    #             'FROM user_invest_position GROUP BY account,contract,direction) AND t.account=%s;'

    query_sql = 'SELECT * FROM user_invest_position WHERE account=%s;'

    # 查询当前日期最新价
    latest_sql = 'SELECT a.quotes_ts,a.contract,a.open_price,a.close_price ' \
                 'FROM dat_futures_daily_quotes AS a ' \
                 'WHERE a.quotes_ts=(SELECT MAX(quotes_ts) FROM dat_futures_daily_quotes);'
    db_conn = FAConnection(conn_name='查询持仓')
    records = db_conn.query(query_sql, param=[account], keep_conn=True)  # 查询仓位
    latest_quotes = db_conn.query(latest_sql)

    holding_df = pd.DataFrame(records)
    # print('持仓记录:', holding_df)
    if holding_df.empty:
        return {'code': r_status.SUCCESS, 'message': '查询账户持仓数据成功!', 'data': {}}
    # 行情数据处理成dict
    quotes_date = datetime.datetime.fromtimestamp(latest_quotes[0]['quotes_ts']).strftime('%Y-%m-%d')
    quotes_data = {f'{item["contract"]}': item["close_price"] for item in latest_quotes}
    holding_df['quote_price'] = holding_df['contract'].apply(lambda x: quotes_data.get(x))
    # 计算保证金占用
    holding_df['bail'] = holding_df['o_price'] * holding_df['o_hands'] * holding_df['hand_amount'] * holding_df['bail_rate']
    # 计算价差
    holding_df['diff_price'] = holding_df.apply(
        lambda x: calculate_price_diff(x['direction'], x['o_price'], x['quote_price']), axis=1)
    # 剩余手数
    # holding_df['h_hands'] = holding_df['o_hands'] - holding_df['c_hands']
    # 计算浮动收益
    holding_df['profit'] = holding_df['diff_price'] * holding_df['o_hands'] * holding_df['hand_amount']
    del holding_df['diff_price']
    # holding_df['create_date'] = holding_df['create_ts'].apply(
    #     lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    holding_df['quote_date'] = [quotes_date for _ in range(holding_df.shape[0])]
    # print(latest_quote_df)
    holding_df['variety_name'] = holding_df['variety_en'].apply(lambda x: VARIETY_NAME.get(x, x))
    holding_df = holding_df[holding_df['o_hands'] > 0]
    hold_list = holding_df.to_dict(orient='records')
    hold_profit = format_number(round(holding_df['profit'].sum(), 2), 2)
    for item in hold_list:
        update_time = item['update_time'].strftime('%Y-%m-%d')
        # item['cur_date'] = item['quote_date'] if update_time > item['quote_date'] else update_time
        item['cur_date'] = update_time
        item['profit'] = format_number(item['profit'], 2)
        item['bail'] = format_number(item['bail'], 2)
    return {'code': r_status.SUCCESS, 'message': '查询账户持仓成功!',
            'data': {'hold_list': hold_list, 'hold_profit': hold_profit}}


class CloseItem(BaseModel):
    hold_id: int
    account: constr(min_length=15, max_length=15)
    close_date: constr(min_length=10, max_length=10)
    close_price: float
    close_hands: conint(ge=1)


# 平仓操作
@investment_exchange_api.post('/closed/', summary='投资号的平仓操作')
async def close_position(close_item: CloseItem = Body(...)):
    # 每日22:58:00~23:59:59不支持平仓
    if no_build_exchange():
        return {'code': r_status.FORBIDDEN, 'message': '周末节假日及系统统计数据期间不支持建仓!'}
    # 验证数据，直接更新
    try:
        close_ts = int(datetime.datetime.strptime(close_item.close_date, '%Y-%m-%d').timestamp())
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': '参数错误,平仓失败!'}

    # 平仓按日期时间
    close_ts = int(datetime.datetime.strptime(datetime.datetime.today().strftime('%Y-%m-%d'), '%Y-%m-%d').timestamp())

    db_conn = FAConnection(conn_name='投资平仓')
    # 当前账户所有持仓记录
    query_sql = 'SELECT a.*,b.r_fund,b.b_fund ' \
                'FROM user_invest_position AS a ' \
                'INNER JOIN user_invest_account AS b ON a.account=b.account ' \
                'WHERE a.account=%s;'
    position_list = db_conn.query(query_sql, param=[close_item.account], keep_conn=True)

    action_obj = None
    account_obj = {}
    for p_item in position_list:
        if p_item['id'] == close_item.hold_id:
            p_item['o_hands'] = p_item['o_hands'] - close_item.close_hands
            action_obj = p_item
            account_obj['r_fund'] = p_item['r_fund']
            account_obj['b_fund'] = p_item['b_fund']
            account_obj['account'] = p_item['account']
    if not action_obj:
        db_conn.close()
        return {'code': r_status.NOT_CONTENT, 'message': '持仓不存在，平仓失败!'}

    # 本次平仓后剩余手数
    leave_hands = action_obj['o_hands']
    if leave_hands < 0:
        db_conn.close()
        return {'code': r_status.VALIDATE_ERROR, 'message': '持仓数量不足，平仓失败!'}

    # 查询权益表，存在则更新数据，不存在添加记录
    profit_exist_sql = 'SELECT id,h_profit,c_profit,total_profit FROM user_invest_profit ' \
                       'WHERE create_ts=%s AND account=%s LIMIT 1;'
    profit_obj = db_conn.query(profit_exist_sql, param=[close_ts, account_obj['account']], fetchone=True, keep_conn=True)[0]

    # 查询最新日期的行情
    # 查询最新行情数据
    latest_sql = 'SELECT a.quotes_ts,a.contract,a.open_price,a.close_price ' \
                 'FROM dat_futures_daily_quotes AS a ' \
                 'WHERE a.quotes_ts=(SELECT MAX(quotes_ts) FROM dat_futures_daily_quotes);'
    db_conn = FAConnection(conn_name='查询持仓')
    latest_quotes = db_conn.query(latest_sql, keep_conn=True)
    # 行情数据处理成dict
    # quotes_date = datetime.datetime.fromtimestamp(latest_quotes[0]['quotes_ts']).strftime('%Y-%m-%d')
    quotes_data = {f'{item["contract"]}': item["close_price"] for item in latest_quotes}

    # 四步事务型操作
    # 1 增加操作明细表
    # 2 修改汇总表
    # 3 处理权益表
    # 4 更新账户表

    # 修改汇总表(暨持仓表)
    position_sql = 'UPDATE user_invest_position SET o_hands=%s WHERE id=%s LIMIT 1;'
    position_param = [leave_hands, action_obj['id']]

    # 本次平仓收益
    price_diff = calculate_price_diff(action_obj['direction'], action_obj['o_price'], close_item.close_price)
    close_profit = price_diff * close_item.close_hands * action_obj['hand_amount']

    # 计算本日持仓浮动收益  (2021-12-28到本日结束后再计算)
    # 计算当前状态下总的浮动收益和保证金占用
    o_bail = 0
    o_profit = 0
    for p_item in position_list:
        quote_price = quotes_data.get(p_item['contract'], p_item['o_price'])
        diff_price = calculate_price_diff(p_item['direction'], p_item['o_price'], quote_price)
        o_profit += diff_price * p_item['hand_amount'] * p_item['o_hands']
        o_bail += p_item['o_price'] * p_item['o_hands'] * p_item['hand_amount'] * p_item['bail_rate']

    r_fund = round(account_obj['r_fund'] + close_profit, 2)  # 当前账户总可用权益
    # if profit_obj:  # 权益表存在更新
    #     today_close_p = close_profit + profit_obj['c_profit']  # 今日平仓操作平仓总收益
    #
    #     profit_sql = 'UPDATE user_invest_profit SET h_profit=%s,c_profit=%s,total_profit=%s,bail=%s ' \
    #                  'WHERE id=%s LIMIT 1;'
    #     profit_param = [round(o_profit, 2), today_close_p, r_fund, round(o_bail, 2), profit_obj['id']]
    # else:  # 不存在，则要增加权益表
    #     profit_sql = 'INSERT INTO user_invest_profit (create_ts,account,h_profit,c_profit,total_profit,bail) ' \
    #                  'VALUES (%s,%s,%s,%s,%s,%s);'
    #     profit_param = [close_ts, close_item.account, round(o_profit, 2), close_profit, r_fund, round(o_bail, 2)]

    # 增加明细表
    c_direction = '平' + action_obj['direction']
    exchange_sql = 'INSERT INTO user_invest_exchange (account,variety_en,contract,direction,hands,hand_amount,' \
                   'price_type,o_price,c_price) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s);'
    exchange_param = [account_obj['account'], action_obj['variety_en'], action_obj['contract'],
                      c_direction, close_item.close_hands, action_obj['hand_amount'], 'close', action_obj['o_price'], close_item.close_price]

    # 更新账户表
    update_account_sql = 'UPDATE user_invest_account SET r_fund=%s,b_fund=%s WHERE account=%s;'
    update_account_param = [r_fund, round(o_bail, 2), close_item.account]

    # 执行事务
    success = db_conn.execute_tasks(sql_list=[exchange_sql, position_sql, update_account_sql],
                                    param_list=[exchange_param, position_param, update_account_param])
    if success:
        return {'code': r_status.SUCCESS, 'message': '操作成功!'}
    else:
        return {'code': r_status.SERVER_ERROR, 'message': '操作失败，服务器错误!'}


# 开仓记录
@investment_exchange_api.get('/opened/', summary='投资号开仓记录')
async def open_position_detail(account: str = Query(..., min_length=15, max_length=15),
                               page: int = Query(1, ge=1), page_size: int = Query(1, ge=1)):
    start_record, offset = (page - 1) * page_size, page_size
    query_sql = 'SELECT SQL_CALC_FOUND_ROWS ' \
                'id,create_time,variety_en,contract,direction,hands,hand_amount,price_type,o_price,c_price ' \
                'FROM user_invest_exchange WHERE account=%s AND c_price=0 ' \
                'ORDER BY create_time DESC LIMIT %s,%s;'
    db_conn = FAConnection(conn_name='查询开仓记录')
    query_ret = db_conn.paginator_query(sql=query_sql, param=[account, start_record, offset], page=page,
                                        page_size=page_size)
    ret_list = query_ret['records']
    # 循环处理数据
    for c_item in ret_list:
        c_item['create_time'] = c_item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        c_item['variety_name'] = VARIETY_NAME.get(c_item['variety_en'], '未知')
    query_ret['opened_list'] = query_ret['records']
    del query_ret['records']
    return {'code': r_status.SUCCESS, 'message': '查询开仓记录成功!', 'data': query_ret}


# 平仓记录
@investment_exchange_api.get('/closed/', summary='投资号平仓记录')
async def close_position_detail(account: str = Query(..., min_length=15, max_length=15),
                                page: int = Query(1, ge=1), page_size: int = Query(1, ge=1)):
    start_record, offset = (page - 1) * page_size, page_size
    # 分页查询平仓记录
    query_sql = 'SELECT SQL_CALC_FOUND_ROWS ' \
                'id,create_time,variety_en,contract,direction,hands,hand_amount,price_type,o_price,c_price ' \
                'FROM user_invest_exchange WHERE account=%s AND c_price>0 ' \
                'ORDER BY create_time DESC LIMIT %s,%s;'
    db_conn = FAConnection(conn_name='查询平仓记录')
    query_ret = db_conn.paginator_query(sql=query_sql, param=[account, start_record, offset], page=page,
                                        page_size=page_size)
    ret_list = query_ret['records']
    # 循环处理数据
    close_profit = 0
    for c_item in ret_list:
        c_item['close_date'] = c_item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        c_item['diff_price'] = calculate_price_diff(c_item['direction'], c_item['o_price'], c_item['c_price'])
        c_item['profit'] = round(c_item['diff_price'] * c_item['hands'] * c_item['hand_amount'], 2)
        del c_item['diff_price']
        c_item['variety_name'] = VARIETY_NAME.get(c_item['variety_en'], '未知')
        close_profit += c_item['profit']
    query_ret['closed_profit'] = format_number(round(close_profit, 2), 2)
    query_ret['closed_list'] = query_ret['records']
    del query_ret['records']
    return {'code': r_status.SUCCESS, 'message': '查询平仓记录成功!', 'data': query_ret}


# 账号的每日权益情况
@investment_exchange_api.get('/daily-profit/', summary='账户的每日收益情况')
async def account_daily_profit(account: str = Query(..., min_length=15, max_length=15)):
    # 查询投资号的每日权益数据
    query_sql = 'SELECT a.create_ts,a.account,a.h_profit,a.c_profit,a.add_fund,a.total_profit,a.bail,b.i_fund ' \
                'FROM user_invest_profit AS a ' \
                'INNER JOIN user_invest_account AS b ON a.account=b.account ' \
                'WHERE a.account=%s;'
    db_conn = FAConnection(conn_name='账号每日权益')
    records = db_conn.query(query_sql, param=[account])
    df = pd.DataFrame(records)
    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '没有数据', 'data': []}

    # 计算当日收益率
    df['profit_rate'] = (df['total_profit'] + df['h_profit'] - df['i_fund']) / df['i_fund']
    df['profit_rate'] = df['profit_rate'].apply(lambda x: round(x * 100, 2))
    df['create_date'] = df['create_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    print(df)
    return {'code': r_status.SUCCESS, 'message': '查询成功!', 'data': df.to_dict(orient='records')}
